import sqlalchemy
from sqlalchemy import create_engine
import time
import pandas as pd
from playwright.sync_api import sync_playwright
from  tqdm import tqdm

TABLE_LISTED_OPEN_ENDED_FUND = "shenge_money_fund_listed_open_ended_fund"
TABLE_FUND_DAY_NET = "shenge_money_fund_day_net"

def connect_db():
    connect_info = 'mysql+pymysql://paqi:chs518518!@rm-bp1v9plylv4340as7qo.mysql.rds.aliyuncs.com:3306/paqi?charset=utf8'
    engine = create_engine(connect_info)
    return engine



def getFundDaysData(page,max,url):
    max = max-1
    page.goto(url)

    #页码
    dataPageLabelNodes = page.query_selector_all("#pagebar > div.pagebtns > label")
    pageSize = 0

    for i in range(len(dataPageLabelNodes)):
        content = dataPageLabelNodes[i].inner_html()
        if content == '下一页':
            pageSize = int(dataPageLabelNodes[i-1].inner_html())

    # pagebar > div.pagebtns > label:nth-child(4)

    details =[]
    for i in range(pageSize):
        if i != 0:
            time.sleep(3)
            page.click("text=下一页")
            time.sleep(3)

        dayDataDetailsNodes = page.query_selector_all("#jztable > table > tbody > tr")

        for dayDataDetailsNode in dayDataDetailsNodes:
            dayTime =dayDataDetailsNode.query_selector("td:nth-child(1)").inner_html()
            net =dayDataDetailsNode.query_selector("td:nth-child(2)").inner_html()
            dayWaveRate =  dayDataDetailsNode.query_selector("td:nth-child(4)").inner_html()
            detail = {}
            detail["dayTime"] =dayTime
            detail["net"] =float(net)
            detail["dayWaveRate"] = str(dayWaveRate)
            if '%' in detail["dayWaveRate"]:
                detail["dayWaveRate"] = detail["dayWaveRate"].replace('%','')
            else:
                detail["dayWaveRate"] = 0
            details.append(detail)
            #print(detail)
            if len(details) > max:
                return details




conn = connect_db()
sql = "SELECT  * FROM %s " % TABLE_LISTED_OPEN_ENDED_FUND
df = pd.read_sql(sql=sql,con=conn,index_col="id")

df_510230= df[['fund_code','fund_detail_url']]

fund_detail_urls =[]
for index,row in df_510230.iterrows():
    fund_detail_urls.append(dict(row))






def  getFundDatas():
    with sync_playwright() as p:
        #all_datas = None
        browser = p.chromium.launch(headless=True, slow_mo=50)
        page =  browser.new_page()
        for url in tqdm(fund_detail_urls):
            dayDatas = getFundDaysData(page,250,url['fund_detail_url'])
            fund_df = pd.DataFrame(dayDatas)
            fund_df['fund_code'] = url['fund_code']
            fund_df.to_sql(TABLE_FUND_DAY_NET, con=conn, index_label="id", if_exists="append")
            # if all_datas is None:
            #     all_datas = fund_df
            # else:
            #     all_datas = pd.concat([all_datas,fund_df],axis=0)





        browser.close()
        page.close()

getFundDatas()
